package com.workcell.workdata.xthings.mapper.mqtt;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.workcell.workdata.xthings.entity.mqtt.BeltScalesData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
@DS("slave_mqtt")
public interface BeltScalesDataMapper extends BaseMapper<BeltScalesData> {
	/**
	 * 查询设备最近的一次记录
	 * @param deviceNo
	 * @param time
	 * @return
	 */
	@Select("SELECT*,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 1 HOUR) ORDER BY `create_time` DESC LIMIT 1)-(\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 1 HOUR) ORDER BY `create_time` ASC LIMIT 1),3)) cumulativeWeightDiffDataHour,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY `create_time` DESC LIMIT 1)-(\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY `create_time` ASC LIMIT 1),3)) cumulativeWeightDiffDataDay,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 1 MONTH) ORDER BY `create_time` DESC LIMIT 1)-(\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_SUB(NOW(),INTERVAL 1 MONTH) ORDER BY `create_time` ASC LIMIT 1),3)) cumulativeWeightDiffDataMonth,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_ADD(CURDATE(),INTERVAL-DAY (CURDATE())+1 DAY) ORDER BY `create_time` DESC LIMIT 1)-(\n" +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=DATE_ADD(CURDATE(),INTERVAL-DAY (CURDATE())+1 DAY) ORDER BY `create_time` ASC LIMIT 1),3)) cumulativeWeightDiffDataSameMonth FROM t_belt_scales_data a WHERE id=(\n" +
			"SELECT MAX(id) aa FROM t_belt_scales_data WHERE slave_device_no=#{deviceNo} AND manufacturer_code=#{manufacturerCode} AND `create_time`>=DATE_SUB(NOW(),INTERVAL #{time} MINUTE));")
	BeltScalesData getLatelyDataInfoByDeviceNo(@Param("deviceNo") String deviceNo, @Param("manufacturerCode") String manufacturerCode, @Param("time") Integer time);

	/**
	 * 获取硬件时间区间内的最近一笔数据
	 * @param deviceNo
	 * @return
	 */
	@Select("select * from t_belt_scales_data where slave_device_no = #{deviceNo} AND manufacturer_code =#{manufacturerCode} and `create_time` >= #{startTime} and `create_time` < #{endTime} order by `create_time` desc LIMIT 1;")
	BeltScalesData getDataHourLineChartInfo(@Param("deviceNo") String deviceNo, @Param("manufacturerCode") String manufacturerCode, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

	/**
	 * 查询报工数据
	 * @param deviceNo
	 * @param manufacturerCode
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Select("SELECT *,( " +
			"SELECT FORMAT(( " +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=#{startTime} AND `create_time`<=#{endTime} " +
			"ORDER BY `create_time` DESC LIMIT 1)-( " +
			"SELECT cumulative_weight FROM t_belt_scales_data WHERE slave_device_no=a.slave_device_no AND manufacturer_code=a.manufacturer_code AND `create_time`>=#{startTime} " +
			"AND `create_time`<=#{endTime} " +
			"ORDER BY `create_time` ASC LIMIT 1),3)) cumulativeWeightDiffDataHour FROM t_belt_scales_data a WHERE id=( " +
			"SELECT MAX(id) aa FROM t_belt_scales_data WHERE slave_device_no=#{deviceNo} AND manufacturer_code=#{manufacturerCode} AND `create_time` >= #{startTime} " +
			"AND `create_time`<=#{endTime} );")
	BeltScalesData getDataHourInfo(@Param("deviceNo") String deviceNo, @Param("manufacturerCode") String manufacturerCode,
								   @Param("startTime") Date startTime, @Param("endTime") Date endTime);

	@Select("SELECT * FROM t_belt_scales_data WHERE slave_device_no=#{deviceNo} AND manufacturer_code=#{manufacturerCode} AND `create_time` >= #{startTime} " +
			"AND `create_time`<=#{endTime} order by create_time asc;")
	List<BeltScalesData> listDataInfo(@Param("deviceNo") String deviceNo, @Param("manufacturerCode") String manufacturerCode,
									  @Param("startTime") Date startTime, @Param("endTime") Date endTime);

}
